package top.zhangjianyong.test.waterrate;

import cn.hutool.core.util.StrUtil;
import org.apache.commons.io.IOUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.*;
import top.zhangjianyong.utils.SXSSFUtils;

import java.awt.*;
import java.io.*;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class WaterRate2 {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        File big15 = new File("C:\\Users\\HDN\\Desktop\\水费\\水费大于15.txt");
        List<String> list = IOUtils.readLines(new FileReader(big15));

        Set<String> set = new HashSet<>(list);


        File file = new File("C:\\Users\\HDN\\Desktop\\水费\\观江苑09.10.xlsx");
        File out = new File("C:\\Users\\HDN\\Desktop\\水费\\观江苑09.10_out.xlsx");

        OPCPackage opcPackage = OPCPackage.open(file);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook(opcPackage);
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();


        for (int i = 1; i <= 12; i++) {
            System.out.println("开始处理" + i + "号楼");
            XSSFSheet xssfSheet = xssfWorkbook.getSheet(i + "号楼");
            SXSSFSheet sxssfSheet = sxssfWorkbook.createSheet(i + "号楼");
            boolean lastFlag = false;

            for (int j = 2; j <= xssfSheet.getLastRowNum(); j++) {

                XSSFRow row = xssfSheet.getRow(j);
                XSSFCell cell = row.getCell(1);
                String stringCellValue = cell.getStringCellValue();

                if (StrUtil.isBlank(stringCellValue)) {
                    if (lastFlag) {
                        System.out.println("房号：" + stringCellValue + ",修改");
                        //改为红色
                        setRed(row);
                    }
                } else {
                    if (set.contains(stringCellValue)) {
                        lastFlag = true;
                        set.remove(stringCellValue);
                        System.out.println("房号：" + stringCellValue + ",修改");
                        //改为红色
                        setRed(row);
                    } else {
                        lastFlag = false;
                    }
                }
            }
            System.out.println("保存文件");
            SXSSFUtils.copySheet(sxssfWorkbook, xssfSheet, sxssfSheet);


        }
        BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(out));

        sxssfWorkbook.write(outputStream);
        outputStream.flush();
        sxssfWorkbook.close();
        xssfWorkbook.close();
    }

    private static void setRed(XSSFRow row) {
        CellStyle rowStyle = row.getRowStyle();
        XSSFColor xssfColor = new XSSFColor(Color.red);
        if (rowStyle != null) {
            rowStyle.setFillBackgroundColor(xssfColor.getIndex());
        }

    }
}
